/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.awt.Font;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.*;

/**
 *
 * @author LUCKYSTARS
 */
public class Connect{
 String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";   
    String url = "jdbc:sqlserver://localhost:1433;databaseName=QLGVDQGJAVA"; 
    static Connection conn = null;
    static Statement st;
    static CallableStatement cstmt;//goi procedure
    //Ket noi CSDL
    public  Connection Connect(String idDB, String passDB) {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,idDB, passDB); 
            System.out.println ("Da ket noi CSDL");
        } catch (ClassNotFoundException ex) {
           JOptionPane.showMessageDialog(null,ex.getMessage(),"Thong bao",JOptionPane.INFORMATION_MESSAGE);
        } catch(SQLException ex)
        {
            JOptionPane.showMessageDialog(null,ex.getMessage(),"Thong bao",JOptionPane.INFORMATION_MESSAGE);
        }
        return conn;    
}
     //Tra ve Ma khi select co dieu khien  
    public String GetMaDK(String select,String Table,String colum,String dk)
    {
        String st = null;
        ResultSet rs=null;
       try{
            String query="Select "+select+" from "+Table+" Where "+colum+"="+"N'"+dk+"'";
             Statement stmt = conn.createStatement();
             rs=stmt.executeQuery(query);
             rs.next();
             st=rs.getString(1);
           }
       catch(SQLException ex)
       {
            JOptionPane.showMessageDialog(null,ex.getMessage(),"Thong bao",JOptionPane.INFORMATION_MESSAGE);
       }
       return st;
    }
    //Tra ve bang du lieu lay tu CSDL (Select)
    public ResultSet GetdataTable(String sqlString)
    {
          ResultSet rs = null;
       try{
            String query=sqlString;
             Statement stmt = conn.createStatement();
             rs=stmt.executeQuery(query);
           }
       catch(SQLException ex)
       {
            JOptionPane.showMessageDialog(null,"Không lấy được dữ liệu","Thong bao",JOptionPane.INFORMATION_MESSAGE);
       }
       return rs;
    }
    //Thuc hien Insert du lieu
    public void Insert(String sqlInsert,ArrayList <String> a,boolean IsPro)
    {
        try {
            if(IsPro)
            {
                cstmt= conn.prepareCall(sqlInsert);
                cstmt.registerOutParameter(1, java.sql.Types.NVARCHAR);
               
               for(int i=1;i<a.size();i++)
                {
                cstmt.setString(i+1, a.get(i)); 
                }
                cstmt.execute();
                String s=cstmt.getString(1);
                  JOptionPane.showMessageDialog(null,s,"Thong bao",JOptionPane.INFORMATION_MESSAGE);                
            }
            else
            {
                st= conn.createStatement();
                st.executeUpdate(sqlInsert);   
            }
        } catch (SQLException ex) {
             JOptionPane.showMessageDialog(null,ex.getMessage(),"Thong bao",JOptionPane.INFORMATION_MESSAGE);  
        }       
    }
    //Thuc hien Update
      public void Update(String sqlUpdate,ArrayList <String> a,boolean IsPro)
    {
        try {
            if(IsPro)
            {
               cstmt= conn.prepareCall(sqlUpdate);
               for(int i=1;i<a.size();i++)
                {
                 cstmt.setString(i, a.get(i));        
                }
                cstmt.executeUpdate();

            }
            else
            {
                st= conn.createStatement();
                st.executeUpdate(sqlUpdate);   
            }
        } catch (SQLException ex) {
             JOptionPane.showMessageDialog(null,ex.getMessage(),"Thong bao",JOptionPane.INFORMATION_MESSAGE);  
        }       
    }
      public  void Delete(String sqlDelete,String a,boolean IsPro )
      {
           try {
             
            if(IsPro)
            {
                cstmt= conn.prepareCall(sqlDelete);
                cstmt.setString(1,a);        
                cstmt.executeUpdate();
            }
            else
            {
                st= conn.createStatement();
                st.executeUpdate(sqlDelete);   
            }
           }catch (SQLException ex) {
             JOptionPane.showMessageDialog(null,"Xóa không thành công!"+ ex.getMessage(),"Thông báo",JOptionPane.INFORMATION_MESSAGE);  
        }      
      }

}
